package com.ltmall.api.dao.impl;

import com.ltmall.api.bean.*;
import com.ltmall.api.dao.ISubUnderwritingDAO;
import com.ltmall.api.util.PageUtil;
import org.hibernate.HibernateException;
import org.hibernate.Query;
import org.hibernate.Session;
import org.springframework.orm.hibernate3.HibernateCallback;

import java.math.BigDecimal;
import java.sql.SQLException;
import java.util.Date;
import java.util.List;

public class SubUnderwritingDAO extends BaseEntityDAO<Order> implements ISubUnderwritingDAO {

    public boolean subUnderwriterAdd(ThSubUnderwriters subUnd) {
        subUnd.setCreatedAt(new Date());
        subUnd.setUpdatedAt(new Date());
        try {
            getHibernateTemplate().persist(subUnd);
        } catch (Exception e) {
            e.printStackTrace();
            return false;
        }
        return true;
    }

    public ThSubUnderwriters subUnderwriterGet(int subUndId) {
        List list = this.getHibernateTemplate().find("from ThSubUnderwriters su where su.id = ?",
            new Object[] { subUndId });
        if (list.size() > 0) {
            return (ThSubUnderwriters) list.get(0);
        }
        return null;
    }

    public int subUnderwriterFansCount(int subUndId) {
        List list = this.getHibernateTemplate().find(
            "from ThSubUnderwriterFans suf where suf.subUnderwriterId = ?",
            new Object[] { subUndId });
        return list.size();
    }

    public int referrerIdGet(int fansId) {
        List list = this.getHibernateTemplate().find(
            "from ThSubUnderwriterFans suf where suf.fansId = ?", new Object[] { fansId });
        if (list.size() == 0) {
            return 0;
        }
        return ((ThSubUnderwriterFans) list.get(0)).getSubUnderwriterId();
    }

    @SuppressWarnings({ "unchecked", "rawtypes" })
    public List<?> subUnderwriterFansList(final int subUndId, final PageUtil page) {
        final String SQL = "SELECT ci.user_id, ci.name, ci.photo, ci.level_id, uf.created_at from th_sub_underwriter_fans uf LEFT JOIN flavor_client_info ci ON uf.fans_id = ci.user_id WHERE uf.sub_underwriter_id = ?";
        List<?> list = this.getHibernateTemplate().execute(new HibernateCallback<List>() {
            public List doInHibernate(Session session) throws HibernateException, SQLException {
                Query query = session.createSQLQuery(SQL);
                query.setParameter(0, subUndId);
                query.setFirstResult((page.getCur_page() - 1) * page.getNumPerPage())
                    .setMaxResults(page.getNumPerPage());
                return query.list();
            }
        });
        return list;
    }

    public boolean subUnderwriterFansAdd(ThSubUnderwriterFans fans) {
        fans.setCreatedAt(new Date());
        fans.setUpdatedAt(new Date());
        try {
            getHibernateTemplate().persist(fans);
        } catch (Exception e) {
            e.printStackTrace();
            return false;
        }
        return true;
    }

    @SuppressWarnings({ "unchecked", "rawtypes" })
    public List<?> subUnderwritingOrderList(final int subUndId, final String fansName,
                                            final PageUtil page) {
        String SQL = "SELECT uo.order_id, uo.state, uo.total_fee, uo.ratio, uo.created_at, ci.user_id, ci.name, ci.photo, o.station, uo.max_profit_share from th_sub_underwriting_orders uo LEFT JOIN flavor_order o ON uo.order_id = o.order_id LEFT JOIN flavor_client_info ci ON o.user_id = ci.user_id WHERE uo.sub_underwriter_id = ?";
        if (fansName != null && !fansName.equals("")) {
            SQL = SQL + " and ci.name like ?";
        }
        final String finalSQL = SQL;
        List<?> list = this.getHibernateTemplate().execute(new HibernateCallback<List>() {
            public List doInHibernate(Session session) throws HibernateException, SQLException {
                Query query = session.createSQLQuery(finalSQL);
                query.setParameter(0, subUndId);
                if (fansName != null && !fansName.equals("")) {
                    query.setParameter(1, "%" + fansName + "%");
                }

                query.setFirstResult((page.getCur_page() - 1) * page.getNumPerPage())
                    .setMaxResults(page.getNumPerPage());
                return query.list();
            }
        });
        return list;
    }

    public boolean subUnderwritingOrderAdd(ThSubUnderwritingOrders order) {
        order.setCreatedAt(new Date());
        order.setUpdatedAt(new Date());
        try {
            getHibernateTemplate().persist(order);
        } catch (Exception e) {
            e.printStackTrace();
            return false;
        }
        return true;
    }

    public boolean subUnderwritingOrderExistence(int orderId) {
        List list = this.getHibernateTemplate().find(
            "from ThSubUnderwritingOrders so where so.orderId = ?", new Object[] { orderId });
        if (list.size() > 0) {
            return true;
        }
        return false;
    }

    public List<ThSubUnderwritingConfigs> subUnderwritingConfigList() {
        List list = this.getHibernateTemplate().find("from ThSubUnderwritingConfigs");
        if (list.size() > 0) {
            return list;
        }
        return null;
    }

    @SuppressWarnings({ "unchecked", "rawtypes" })
    public Object[] subUnderwriterInfo(final int fansId) {
        final String SQL = "SELECT ci.user_id, ul.sub_underwriting_ratio from th_sub_underwriter_fans suf LEFT JOIN flavor_client_info ci ON suf.sub_underwriter_id = ci.user_id LEFT JOIN th_user_levels ul ON ci.level_id = ul.id WHERE suf.fans_id = ?";
        List<?> list = this.getHibernateTemplate().execute(new HibernateCallback<List>() {
            public List doInHibernate(Session session) throws HibernateException, SQLException {
                Query query = session.createSQLQuery(SQL);
                query.setParameter(0, fansId);
                return query.list();
            }
        });

        if (list.size() > 0) {
            return (Object[]) (list.get(0));
        }
        return null;
    }

    public boolean subUnderwriterUpdate(ThSubUnderwriters subUnd) {
        subUnd.setUpdatedAt(new Date());
        try {
            getHibernateTemplate().update(subUnd);
        } catch (Exception e) {
            e.printStackTrace();
            return false;
        }
        return true;
    }

    public ThSubUnderwriterQrCodes subUnderwriterQRCodeGet(int subUndId) {
        List list = this.getHibernateTemplate().find(
            "from ThSubUnderwriterQrCodes suqc where suqc.subUnderwriterId = ?",
            new Object[] { subUndId });
        if (list.size() > 0) {
            for (Object object : list) {
                ThSubUnderwriterQrCodes qrCodes = (ThSubUnderwriterQrCodes) object;
                if (qrCodes.getUpdatedAt() != null) {
                    return qrCodes;
                }
            }
        }
        return null;
    }

    public ThSubUnderwriterQrCodes subUnderwriterLimitQrCodes(int subUndId) {
        List list = this.getHibernateTemplate().find(
            "from ThSubUnderwriterQrCodes suqc where suqc.subUnderwriterId = ?",
            new Object[] { subUndId });
        if (list.size() > 0) {
            for (Object object : list) {
                ThSubUnderwriterQrCodes qrCodes = (ThSubUnderwriterQrCodes) object;
                if (qrCodes.getUpdatedAt() == null) {
                    return qrCodes;
                }
            }
        }
        return null;
    }

    public boolean subUnderwriterQRCodeSaveOrUpdate(ThSubUnderwriterQrCodes qrCode) {
        try {
            getHibernateTemplate().saveOrUpdate(qrCode);
        } catch (Exception e) {
            e.printStackTrace();
            return false;
        }
        return true;
    }

    public boolean subUnderwriterQRCodeImageUpdate(int subUndId, String image) {
        String hql = "update ThSubUnderwriterQrCodes su set su.syntheticImage = ? where su.subUnderwriterId = ?";
        try {
            Query query = getSession().createQuery(hql);
            query.setParameter(0, image);
            query.setParameter(1, subUndId);
            int temp = query.executeUpdate();
            if (temp == 1) {
                return true;
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
        return false;
    }

    public boolean subUnderwriterTotalOutcomeUpdate(int subUndId, BigDecimal totalOutcome) {
        String hql = "update ThSubUnderwriters su set su.totalOutcome = ? where su.id = ?";
        try {
            Query query = getSession().createQuery(hql);
            query.setParameter(0, totalOutcome);
            query.setParameter(1, subUndId);
            int temp = query.executeUpdate();
            if (temp == 1) {
                return true;
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
        return false;
    }

    public boolean wechatRedPackOutcomeAdd(ThRedPackOutcomes redPack) {
        redPack.setCreatedAt(new Date());
        try {
            getHibernateTemplate().persist(redPack);
        } catch (Exception e) {
            e.printStackTrace();
            return false;
        }
        return true;
    }

    public boolean wechatRedPackOutcomeUpdate(ThRedPackOutcomes redPack) {
        redPack.setUpdatedAt(new Date());
        try {
            getHibernateTemplate().update(redPack);
        } catch (Exception e) {
            e.printStackTrace();
            return false;
        }
        return true;
    }

    public boolean subUnderwritingIncomeAdd(ThSubUnderwritingIncomes income) {
        income.setCreatedAt(new Date());
        try {
            getHibernateTemplate().persist(income);
        } catch (Exception e) {
            e.printStackTrace();
            return false;
        }
        return true;
    }

    public boolean subUnderwritingOutcomeAdd(ThSubUnderwritingOutcomes outcome) {
        outcome.setCreatedAt(new Date());
        try {
            getHibernateTemplate().persist(outcome);
        } catch (Exception e) {
            e.printStackTrace();
            return false;
        }
        return true;
    }

    @SuppressWarnings({ "unchecked", "rawtypes" })
    public List<ThSubUnderwritingOutcomes> subUnderwritingOutcomeList(final int subUndId,
                                                                      final PageUtil page) {
        final String HQL = "FROM ThSubUnderwritingOutcomes so WHERE so.userId = ? ORDER BY so.id DESC";
        List<?> list = this.getHibernateTemplate().execute(new HibernateCallback<List>() {
            public List doInHibernate(Session session) throws HibernateException, SQLException {
                Query query = session.createQuery(HQL);
                query.setParameter(0, subUndId);
                query.setFirstResult((page.getCur_page() - 1) * page.getNumPerPage())
                    .setMaxResults(page.getNumPerPage());
                return query.list();
            }
        });
        return (List<ThSubUnderwritingOutcomes>) list;
    }

    @SuppressWarnings({ "unchecked", "rawtypes" })
    public List<ThSubUnderwritingIncomes> subUnderwritingIncomeList(final int subUndId,
                                                                    final int type,
                                                                    final PageUtil page) {
        String mainHQL = null;
        if (type == 0) {
            mainHQL = "FROM ThSubUnderwritingIncomes so WHERE so.userId = ? ORDER BY so.id DESC";
        } else {
            mainHQL = "FROM ThSubUnderwritingIncomes so WHERE so.userId = ? and so.channelType = ? ORDER BY so.id DESC";
        }
        final String HQL = mainHQL;

        List<?> list = this.getHibernateTemplate().execute(new HibernateCallback<List>() {
            public List doInHibernate(Session session) throws HibernateException, SQLException {
                Query query = session.createQuery(HQL);
                query.setParameter(0, subUndId);
                if (type != 0) {
                    query.setParameter(1, type);
                }
                query.setFirstResult((page.getCur_page() - 1) * page.getNumPerPage())
                    .setMaxResults(page.getNumPerPage());
                return query.list();
            }
        });
        return (List<ThSubUnderwritingIncomes>) list;
    }

    public List<?> subUnderwriterFansLevelUpIncomeList(final int subUndId, final PageUtil page) {
        final String SQL = "SELECT flui.id, flui.fans_id, flui.fans_level_id, flui.income, flui.created_at, ci.name, ci.photo, ul.title from th_fans_level_up_incomes flui LEFT JOIN flavor_client_info ci ON flui.fans_id = ci.user_id LEFT JOIN th_user_levels ul ON ci.level_id = ul.id WHERE flui.sub_underwriter_id = ? and flui.type = 1";
        List<?> list = this.getHibernateTemplate().execute(new HibernateCallback<List>() {
            public List doInHibernate(Session session) throws HibernateException, SQLException {
                Query query = session.createSQLQuery(SQL);
                query.setParameter(0, subUndId);
                query.setFirstResult((page.getCur_page() - 1) * page.getNumPerPage())
                    .setMaxResults(page.getNumPerPage());
                return query.list();
            }
        });

        return list;
    }

    public BigDecimal subUnderwritingOrderTotalSalesAmount(final int subUndId) {
        final String SQL = "SELECT SUM(suo.total_fee) FROM th_sub_underwriting_orders suo LEFT JOIN flavor_order fo ON suo.order_id = fo.order_id WHERE fo.station >= 2 AND fo.station <= 6 AND suo.sub_underwriter_id = ?";
        List<?> list = this.getHibernateTemplate().execute(new HibernateCallback<List>() {
            public List doInHibernate(Session session) throws HibernateException, SQLException {
                Query query = session.createSQLQuery(SQL);
                query.setParameter(0, subUndId);
                return query.list();
            }
        });

        if (list.size() > 0 && list.get(0) != null) {
            return (BigDecimal) list.get(0);
        }
        return BigDecimal.valueOf(0);
    }

    public BigDecimal subUnderwritingOrderTotalEstimatedIncome(final int subUndId) {
        final String SQL = "SELECT SUM(suo.total_fee * suo.ratio) FROM th_sub_underwriting_orders suo LEFT JOIN flavor_order fo ON suo.order_id = fo.order_id WHERE fo.station >= 2 AND fo.station <= 6 AND suo.sub_underwriter_id = ?";
        List<?> list = this.getHibernateTemplate().execute(new HibernateCallback<List>() {
            public List doInHibernate(Session session) throws HibernateException, SQLException {
                Query query = session.createSQLQuery(SQL);
                query.setParameter(0, subUndId);
                return query.list();
            }
        });

        if (list.size() > 0 && list.get(0) != null) {
            return (BigDecimal) list.get(0);
        }
        return BigDecimal.valueOf(0);
    }

    public int fansCountGetByLevelId(final int level, final int userID) {
        final String SQL = "SELECT COUNT(*) FROM th_sub_underwriters su INNER JOIN th_sub_underwriter_fans suf ON su.id = suf.fans_id LEFT JOIN flavor_client_info ci ON su.id = ci.user_id WHERE ci.level_id = ? AND suf.created_at < CURRENT_TIME AND suf.sub_underwriter_id = ? AND su.id not in (select flui.fans_id from th_fans_level_up_incomes flui)";
        List<?> list = this.getHibernateTemplate().execute(new HibernateCallback<List>() {
            public List doInHibernate(Session session) throws HibernateException, SQLException {
                Query query = session.createSQLQuery(SQL);
                query.setParameter(0, level);
                query.setParameter(1, userID);
                return query.list();
            }
        });
        if (list.size() > 0 && list.get(0) != null) {
            return Integer.parseInt((String) list.get(0).toString());
        }
        return 0;
    }

    @SuppressWarnings({ "unchecked", "rawtypes" })
    public List<?> rewardGotFansInfoGetByUserId(final int userID, final PageUtil page) {
        // TODO Auto-generated method stub
        final String SQL = "SELECT ci.photo,ci.name,ci.level_id,ci.user_id FROM th_sub_underwriters su INNER JOIN th_sub_underwriter_fans suf ON su.id = suf.fans_id LEFT JOIN flavor_client_info ci ON su.id = ci.user_id WHERE ci.level_id > 0 AND suf.created_at < CURRENT_TIME AND suf.sub_underwriter_id = ? AND su.id not in (select flui.fans_id from th_fans_level_up_incomes flui)";
        List<?> list = this.getHibernateTemplate().execute(new HibernateCallback<List>() {
            public List doInHibernate(Session session) throws HibernateException, SQLException {
                Query query = session.createSQLQuery(SQL);
                query.setParameter(0, userID);
                query.setFirstResult(0);
                query.setFirstResult((page.getCur_page() - 1) * page.getNumPerPage())
                    .setMaxResults(page.getNumPerPage());
                return query.list();
            }
        });
        return list;
    }

    public int myFansCountGetByMonth(final int level, final int userID, final Date startTime,
                                     final Date endTime) {
        // TODO Auto-generated method stub
        final String SQL = "select count(*) from th_fans_level_up_incomes flui where flui.fans_level_id = ? AND flui.sub_underwriter_id = ? AND flui.type = 1 AND flui.created_at between ? AND ?";
        List<?> list = this.getHibernateTemplate().execute(new HibernateCallback<List>() {
            public List doInHibernate(Session session) throws HibernateException, SQLException {
                Query query = session.createSQLQuery(SQL);
                query.setParameter(0, level);
                query.setParameter(1, userID);
                query.setParameter(2, startTime);
                query.setParameter(3, endTime);
                return query.list();
            }
        });
        if (list.size() > 0 && list.get(0) != null) {
            return Integer.parseInt((String) list.get(0).toString());
        }
        return 0;
    }

    @SuppressWarnings({ "unchecked", "rawtypes" })
    public List<?> myRewardHistoryFansInfoGetByMonth(final int userID, final PageUtil page) {
        final String SQL = "SELECT ci.name,ci.photo,flui.fans_level_id,flui.income,flui.created_at,flui.level FROM th_fans_level_up_incomes flui INNER JOIN flavor_client_info ci ON flui.fans_id = ci.user_id WHERE flui.sub_underwriter_id = ? AND flui.type = 1 ";
        List<?> list = this.getHibernateTemplate().execute(new HibernateCallback<List>() {
            public List doInHibernate(Session session) throws HibernateException, SQLException {
                Query query = session.createSQLQuery(SQL);
                query.setParameter(0, userID);
                query.setFirstResult(0);
                query.setFirstResult((page.getCur_page() - 1) * page.getNumPerPage())
                    .setMaxResults(page.getNumPerPage());
                return query.list();
            }
        });
        return list;
    }

    @SuppressWarnings({ "unchecked", "rawtypes" })
    public List<?> directorPrivilegeOrderIncomesInfoGetByMonth(final String type ,final int userID, final PageUtil page) {
         String sqlStr = null ;
        if(type=="0"){
            sqlStr = "SELECT info.name,info.photo, incomes.amount, incomes.created_at ,incomes.level,incomes.channel_type "
                    + "FROM th_sub_underwriting_incomes AS incomes "
                    + "JOIN flavor_director_privilege_order AS orders ON incomes.channel_id = orders.id "
                    + "JOIN flavor_client_info AS info ON orders.user_id = info.USER_ID "
                    + "WHERE incomes.user_id = ?";
        }else if(type=="3"){
            sqlStr = "SELECT info.name,info.photo, incomes.amount, incomes.created_at ,incomes.level,incomes.channel_type "
                    + "FROM th_sub_underwriting_incomes AS incomes "
                    + "JOIN flavor_director_privilege_order AS orders ON incomes.channel_id = orders.id "
                    + "JOIN flavor_client_info AS info ON orders.user_id = info.USER_ID "
                    + "WHERE incomes.channel_type = 3 AND incomes.user_id = ?";
        }
        
        final String SQL=sqlStr+" order by incomes.created_at desc";
        
        List<?> list = this.getHibernateTemplate().execute(new HibernateCallback<List>() {
            public List doInHibernate(Session session) throws HibernateException, SQLException {
                Query query = session.createSQLQuery(SQL);
                query.setParameter(0, userID);
                query.setFirstResult(0);
                query.setFirstResult((page.getCur_page() - 1) * page.getNumPerPage())
                    .setMaxResults(page.getNumPerPage());
                return query.list();
            }
        });
        return list;
    }

    public List<ClientInfo> fansInfoGetByLevelId(final int level, final int userID,
                                                 final PageUtil page) {
        // TODO Auto-generated method stub
        final String SQL = "SELECT ci.name,ci.photo,ci.level_id FROM th_sub_underwriters su INNER JOIN th_sub_underwriter_fans suf ON su.id = suf.fans_id LEFT JOIN flavor_client_info ci ON su.id = ci.user_id WHERE ci.level_id = ? AND su.created_at < CURRENT_DATE AND suf.sub_underwriter_id = ? AND su.id not in (select flui.fans_id from th_fans_level_up_incomes flui)";
        List<?> list = this.getHibernateTemplate().execute(new HibernateCallback<List>() {
            public List doInHibernate(Session session) throws HibernateException, SQLException {
                Query query = session.createSQLQuery(SQL);
                query.setParameter(0, level);
                query.setParameter(1, userID);
                query.setFirstResult(0);
                query.setFirstResult((page.getCur_page() - 1) * page.getNumPerPage())
                    .setMaxResults(page.getNumPerPage());
                return query.list();
            }
        });
        return null;
    }

    public Object[] referrerInfo(final int userId) {
        final String SQL = "select ci.user_id, ci.name from th_sub_underwriter_fans suf left join flavor_client_info ci on suf.sub_underwriter_id = ci.user_id where suf.fans_id = ?";
        List<?> list = this.getHibernateTemplate().execute(new HibernateCallback<List>() {
            public List doInHibernate(Session session) throws HibernateException, SQLException {
                Query query = session.createSQLQuery(SQL);
                query.setParameter(0, userId);
                return query.list();
            }
        });
        if (list.size() > 0) {
            return (Object[]) list.get(0);
        }
        return null;
    }

    public BigDecimal getProfitShare(final int userID) {
        final String SQL = "select SUM(max_profit_share) from th_sub_underwriting_orders suo LEFT JOIN flavor_order fo ON suo.order_id = fo.order_id WHERE fo.station >= 2 AND fo.station <= 6 AND suo.sub_underwriter_id = ?";
        Session session = getSession();
        Query query = session.createSQLQuery(SQL);
        query.setParameter(0, userID);
        List<?> list = query.list();
        if (list.size() > 0) {
            if (list.get(0) == null) {
                return new BigDecimal(0);
            } else {
                return ((BigDecimal) list.get(0)).divide(new BigDecimal(100));
            }
        } else {
            return null;
        }
    }

    public BigDecimal getEstimatedProfitShare(final int userID) {
        final String SQL = "select SUM(max_profit_share) "
                           + "from th_sub_underwriting_orders suo "
                           + "LEFT JOIN flavor_order fo ON suo.order_id = fo.order_id "
                           + "WHERE (fo.station in (2,3,4,5) OR (fo.station = 6 AND NOW() < date_add(suo.created_at, INTERVAL 14 DAY))) "
                           + "AND suo.sub_underwriter_id = ?";
        Session session = getSession();
        Query query = session.createSQLQuery(SQL);
        query.setParameter(0, userID);
        List<?> list = query.list();
        if (list.size() > 0) {
            if (list.get(0) == null) {
                return new BigDecimal(0);
            } else {
                return ((BigDecimal) list.get(0)).divide(new BigDecimal(100));
            }
        } else {
            return null;
        }
    }

    public BigDecimal getOrderProfitShare(final int orderID) {
        final String SQL = "SELECT SUM(ts.GOODS_PROFIT_SHARE * foc.AMOUNT) "
                           + "FROM th_sku ts, flavor_order_content foc "
                           + "WHERE ts.ID = foc.SKU_ID AND foc.ORDER_ID = ?";
        Session session = getSession();
        Query query = session.createSQLQuery(SQL);
        query.setParameter(0, orderID);
        List<?> list = query.list();
        releaseSession(session);
        if (list.size() > 0) {
            if (list.get(0) == null) {
                return new BigDecimal(0);
            } else {
                return ((BigDecimal) list.get(0)).divide(new BigDecimal(100));
            }
        } else {
            return null;
        }
    }

    public ClientInfo getClientInfoByFansId(int fansId) {
        final String SQL = "SELECT * FROM flavor_client_info JOIN  th_sub_underwriter_fans ON "
                           + "sub_underwriter_id = user_id WHERE fans_id = ?";
        Session session = getSession();
        Query query = session.createSQLQuery(SQL).addEntity(ClientInfo.class);
        query.setParameter(0, fansId);
        @SuppressWarnings("unchecked")
        List<ClientInfo> list = query.list();
        if (list.size() > 0) {
            return list.get(0);
        }
        return null;
    }

    public ThUserLevels getThUserLevelsById(int id) {
        Session session = getSession();
        try {
            return (ThUserLevels) session.get(ThUserLevels.class, id);
        } catch (Exception e) {
            e.printStackTrace();
            return null;
        } finally {
            releaseSession(session);
        }
    }
}
